﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace HotelManagement
{
    public partial class GetExtraService : Form
    {
        public int rs_id = 0;
        public int es_id;
        SqlConnection Cnn = new SqlConnection(Globals.DBConnection());
        DataSet ds = new DataSet();
        SqlDataAdapter da;



        public GetExtraService()
        {
            InitializeComponent();
        }

        private void GetExtraService_Load(object sender, EventArgs e)
        {
            da = new SqlDataAdapter("select es_id as [ردیف],es_rs_id as [شماره نامه رزرو],s_name as [نوع سرویس],es_count as [تعداد] ,es_price as [هزینه] from Extra_Service,Service WHERE es_s_id=s_id AND es_rs_id=@s_id", Cnn);
            da.SelectCommand.Parameters.AddWithValue("@s_id", rs_id);
            da.Fill(ds);
            DgViewExtraService.DataSource = ds.Tables[0];

            DgViewExtraService.Columns[0].Visible = false;

            DataSet ComboBoxDS = new DataSet();
            SqlDataAdapter ComboBoxDA;
            ComboBoxDA = new SqlDataAdapter("select s_id,s_name from Service", Cnn);
            ComboBoxDA.Fill(ComboBoxDS);
            ServiceTypeComboBox.DataSource = ComboBoxDS.Tables[0];
            ServiceTypeComboBox.DisplayMember = "s_name";
            ServiceTypeComboBox.ValueMember = "s_id";

        }

        private void RsIDSearchBTN_Click(object sender, EventArgs e)
        {
            ReserveSerach rs = new ReserveSerach();
            rs.ShowDialog();
            rs_id = rs.Rs_ID;
            RsIdTxt.Text = rs_id.ToString();


            da = new SqlDataAdapter("select es_id as [ردیف],es_rs_id as [شماره نامه رزرو],s_name as [نوع سرویس],es_count as [تعداد] ,es_price as [هزینه] from Extra_Service,Service WHERE es_s_id=s_id AND es_rs_id=@s_id", Cnn);
            da.SelectCommand.Parameters.AddWithValue("@s_id", rs_id);
            ds.Clear();
            da.Fill(ds);
            DgViewExtraService.DataSource = ds.Tables[0];
        }

        private void SaveESDataBtn_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(RsIdTxt.Text) && !string.IsNullOrEmpty(NumberOfServiceTxt.Text) && !string.IsNullOrEmpty(ServiceTypeComboBox.Text))
            {
                Int64 price;
                SqlCommand PriceSelect = new SqlCommand("select s_price from Service where s_id=@s_id", Cnn);
                SqlDataReader DR;
                PriceSelect.Parameters.AddWithValue("@s_id", ServiceTypeComboBox.SelectedValue);
                Cnn.Open();
                DR = PriceSelect.ExecuteReader();
                DR.Read();
                price = Int64.Parse(DR[0].ToString());
                Cnn.Close();

                SqlCommand SaveINDB = new SqlCommand("INSERT INTO Extra_Service(es_rs_id,es_s_id,es_count,es_price) values (@rs_id,@s_id,@count,@price)", Cnn);
                SaveINDB.Parameters.AddWithValue("@rs_id", int.Parse(RsIdTxt.Text.Trim()));
                SaveINDB.Parameters.AddWithValue("@s_id", (int)ServiceTypeComboBox.SelectedValue);
                SaveINDB.Parameters.AddWithValue("@count", int.Parse(NumberOfServiceTxt.Text));
                SaveINDB.Parameters.AddWithValue("@price", ((Int64)(price * int.Parse(NumberOfServiceTxt.Text))).ToString());

                Cnn.Open();
                SaveINDB.ExecuteNonQuery();
                Cnn.Close();

                //////update factor
                // find the last id for save in factor table
                SqlCommand f_idSelect = new SqlCommand("select f_id from Factor where f_rs_id=@rs_id", Cnn);
                f_idSelect.Parameters.AddWithValue("@rs_id", int.Parse(RsIdTxt.Text.Trim()));
                SqlDataReader DR1;
                Cnn.Open();
                DR1 = f_idSelect.ExecuteReader();
                DR1.Read();
                int f_id = (int)DR1[0];
                Cnn.Close();

                SqlCommand UpdateFactor = new SqlCommand("UPDATE Factor SET f_tprice= COALESCE(((select SUM(rs_tprice) from Reserve where rs_id = @es_rs_id) + (select SUM(es_price) from Extra_Service WHERE es_rs_id =@es_rs_id)),(select SUM(rs_tprice) from Reserve where rs_id = @es_rs_id)) WHERE f_id=@f_id", Cnn);
                UpdateFactor.Parameters.AddWithValue("@es_rs_id", int.Parse(RsIdTxt.Text.Trim()));
                UpdateFactor.Parameters.AddWithValue("@f_id", f_id);

                Cnn.Open();
                UpdateFactor.ExecuteNonQuery();
                Cnn.Close();
                //////update factor

                ds.Clear();
                da.Fill(ds);
                DgViewExtraService.DataSource = ds.Tables[0];
            }
            else
            {
                MessageBox.Show("لطفا همه قسمت ها را تکمیل نمایید");
            }
        }

        private void DgViewExtraService_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            es_id = (int)DgViewExtraService.CurrentRow.Cells[0].Value;

            DeleteESDataBtn.Enabled = true;
        }

        private void DeleteESDataBtn_Click(object sender, EventArgs e)
        {
            SqlCommand DeleteCMD = new SqlCommand("DELETE FROM Extra_Service WHERE es_id=@es_id", Cnn);
            DeleteCMD.Parameters.AddWithValue("@es_id", es_id);
            Cnn.Open();
            DeleteCMD.ExecuteNonQuery();
            Cnn.Close();

            ////// update factor
            // find the last id for save in factor table
            SqlCommand f_idSelect = new SqlCommand("select f_id from Factor where f_rs_id=@rs_id", Cnn);
            f_idSelect.Parameters.AddWithValue("@rs_id", int.Parse(RsIdTxt.Text.Trim()));
            SqlDataReader DR1;
            Cnn.Open();
            DR1 = f_idSelect.ExecuteReader();
            DR1.Read();
            int f_id = (int)DR1[0];
            Cnn.Close();

            SqlCommand UpdateFactor = new SqlCommand("UPDATE Factor SET f_tprice= COALESCE(((select SUM(rs_tprice) from Reserve where rs_id = @es_rs_id) + (select SUM(es_price) from Extra_Service WHERE es_rs_id =@es_rs_id)),(select SUM(rs_tprice) from Reserve where rs_id = @es_rs_id)) WHERE f_id=@f_id", Cnn);
            UpdateFactor.Parameters.AddWithValue("@es_rs_id", int.Parse(RsIdTxt.Text.Trim()));
            UpdateFactor.Parameters.AddWithValue("@f_id", f_id);

            Cnn.Open();
            UpdateFactor.ExecuteNonQuery();
            Cnn.Close();
            //////update factor

            ds.Clear();
            da.Fill(ds);
            DgViewExtraService.DataSource = ds.Tables[0];

            DeleteESDataBtn.Enabled = false;
        }


    }
}
